clear all
set more off
set seed 6615230

*******************************************************************
//// 11-BUILD_OFFICER: CONSTRUCT MASTER DATASET OF FHP OFFICERS ////
*******************************************************************



*************************
/// LIST OF OFFICERS ///
************************

use "${out}/2-stopsfhp", clear

drop if mi(officerid)
keep officerid
duplicates drop

qui compress
saveold "${temp}/LIST", replace


***********************************
/// FDLE EMPLOYMENT SPELLS DATA ///
***********************************

import excel using "${raw}/FDLE/employment.xlsx", clear first
keep if employ_class=="LE"
gen officerid = person_nbr

merge m:1 officerid using "${temp}/LIST", keep(3) nogen

gen firstle    = employ_start_date 
gen firstleft  = employ_start_date if employ_type == "FT"
gen firstfhp   = employ_start_date if agcy_nbr == 9
gen firstfhpft = employ_start_date if agcy_nbr == 9 & employ_type == "FT"
collapse (min) firstle-firstfhpft, by(officerid)

format %td firstle 
label var firstle "First LE Start"
format %td firstleft 
label var firstleft "First LE Start (FT)"
format %td firstfhp
label var firstfhp "FHP Start"
format %td firstfhpft 
label var firstfhpft "FHP Start (FT)"

qui compress
saveold "${temp}/EMP", replace


***************************
/// FDLE PERSONNEL DATA ///
***************************

import excel using "${raw}/FDLE/person_data2.xlsx", clear first

gen officerid = person_nbr
duplicates drop officerid, force

merge 1:1 officerid using "${temp}/LIST", keep(3) nogen

gen female = (sex_code == "F")
label var female "Female"

replace race_code = trim(upper(race_code))
gen frace_code = substr(race_code,1,1)

gen race = "O" 
replace race = "B" if frace_code == "B"
replace race = "H" if frace_code == "H"
replace race = "W" if frace_code == "W"|frace_code == "C"
label var race "Race"

gen edlevel = education_level
label var edlevel "Education Level"
gen college = (edlevel == "A" | edlevel == "B" | edlevel == "J" | edlevel == "M" | edlevel == "MD" | edlevel == "P")
label var college "Any College"
gen baplus = (edlevel == "B" | edlevel == "J" | edlevel == "M" | edlevel == "MD" | edlevel == "P")
label var baplus "BA+" 

gen yob = birth_year
label var yob "Year of Birth"

keep officerid female yob race edlevel college baplus
order officerid female yob race edlevel college baplus

qui compress
saveold "${temp}/PERSON", replace


***************************************************
/// EXPERIENCE MEASURE FOR LATE SAMPLE: TICKETS ///
***************************************************

use "${out}/2-stopsfhp", clear

drop if mi(officerid)
keep if viol_code == 575
keep if year <= 2009

gen exp1 = 1 
collapse (sum) exp1, by(officerid) fast
label var exp1 "Speeding Stops, 2005-2009" 

qui compress
saveold "${temp}/EXP", replace


***************
/// COMPILE ///
***************

use "${temp}/LIST", clear
merge 1:1 officerid using "${temp}/PERSON", keep(1 3) nogen
merge 1:1 officerid using "${temp}/EMP", keep(1 3) nogen
merge 1:1 officerid using "${temp}/EXP", keep(1 3) nogen


**********************************
/// OTHER EXPERIENCE MEASURES? ///
**********************************

gen temp1 = "01/01/2007"
gen temp2 = date(temp1,"MDY")
gen exp2  = int((temp2 - firstfhp)/365.25)
replace exp2 = 0 if mi(exp2) | exp2<0
label var exp2 "Years on Force, 1/2007" 
drop temp1 temp2 

gen temp1 = "01/01/2010"
gen temp2 = date(temp1,"MDY")
gen exp3  = int((temp2 - firstfhp)/365.25) 
replace exp3 = 0 if mi(exp3) | exp3<0 
label var exp3 "Years on Force, 1/2010"
drop temp1 temp2 


**********************************
/// STORE + CLEAN UP ///
**********************************

qui compress
saveold "${out}/3-officer", replace



rm "${temp}/LIST.dta"
rm "${temp}/PERSON.dta"
rm "${temp}/EMP.dta"
rm "${temp}/EXP.dta"








